from django.shortcuts import render

from .models import BookInfo, HeroInfo

"""增
save()
create()  # 它内部封装了save
"""
# book = BookInfo()  # 创建模型对象
# book.btitle = '梦三国'
# book.bpub_date = '2001-11-11'
# book.save()

# book = BookInfo(
#     btitle='西2',
#     bpub_date='1999-11-11'
# )
# book.save()

# hero = HeroInfo(
#     hname='孙悟空',
#     hgender=1,
#     # hbook='外键指向的模型对象',
#     # hbook=book,
#     # hbook_id='外键指向的模型对象id值'
#     hbook_id=book.id
# )
# hero.save()

# 模型类名.objects 代表获取当前模型管理器
# book = BookInfo.objects.create(
#     btitle='王者',
#     bpub_date='2001-12-11'
# )







"""删
模型对象.delete() 删除单一
filter().delete() 删除任意数量个
"""
# book = BookInfo.objects.get(id=6)
# book.delete()
# BookInfo.objects.filter(id__gte=7).delete()

# book = BookInfo.objects.get(id=4)
# book.is_delete = True
# book.save()
"""改
save()  修改单一
update()  批量修改
"""
# book = BookInfo.objects.get(btitle='西游记1')
# book.btitle='梦三国'
# book.save()  # id

# books = BookInfo.objects.filter(bread=100).update(bread=0)
# books = BookInfo.objects.all().update(bread=0)
# print(books)



"""查"""
"""
基本查询
get()  # 查单一
all()  # 无条查所有
count()  # 查询数据条数
"""
# book = BookInfo.objects.get(id=1)
# book = BookInfo.objects.all()
# count = BookInfo.objects.count()
# print(count)


"""
过滤查询
filter()  # 查询满足条件的所有记录,如果查询不到也不会报错,会返回一个空的QuerySet(), 只会返回QuerySet
exclude()  # 查询满足条件以外的所有记录, 只会返回QuerySet
get()  # 只能查单一(必须要查到一个),不然就报错, 能查到必定返回单个模型对象
条件表达式语法格式: 字段名__运算符=常量值
"""                             # 字段名__运算符=常量值
# book = BookInfo.objects.filter(btitle__contains='传')
# book = BookInfo.objects.get(id=1)
# book = BookInfo.objects.filter(id=1)
# book = BookInfo.objects.filter(btitle__contains='湖')
# book = BookInfo.objects.filter(btitle__endswith='部')
# book = BookInfo.objects.filter(btitle__startswith='天')

# book = BookInfo.objects.filter(btitle__isnull=False)
# book = BookInfo.objects.filter(id__in=[2, 4])
# book = BookInfo.objects.filter(id__gt=2)
# book = BookInfo.objects.filter(id__lte=2)
# book = BookInfo.objects.exclude(id=3)
# book = BookInfo.objects.filter(bpub_date__year='1987')
# book = BookInfo.objects.filter(bpub_date__gt='1990-01-01')

"""F对象可以实现两个字段之间的比较, F对象还支付运算  * / + -"""
# from django.db.models import F, Q
# book = BookInfo.objects.filter(bread__gt=F('bcomment'))
# book = BookInfo.objects.filter(bread__gt=F('bcomment') * 2)
# print(book)

"""
Q对象可以进行逻辑判断  and,  or, not,
Q对象主要可以实现逻辑或
"""
# book = BookInfo.objects.filter(id__lt=3, bread__gt=10)  # 此写时同时满足条件的数据
# book = BookInfo.objects.filter(Q(id__lt=3)| Q(bread__gt=20))  # 逻辑或 只要满足其中一个条件的都要
# print(book)

"""聚合函数: 对指定列的数据进行 运算 求合 求平均  max min
返回字典 {聚合字段名__聚合函数名小写: value}
"""
from django.db.models import Sum, Max, Avg

# sum_number = BookInfo.objects.aggregate(Sum('bread'))
# sum_number1 = BookInfo.objects.aggregate(Max('bread'))
# print(sum_number)
# print(sum_number1)


"""
排序  order_by('字段名')
从小到大  默认升序  order_by('字段名')
从大到小  降序   order_by('-字段名')
"""
# books = BookInfo.objects.order_by('-bread')
# print(books)

"""
关联查询: 当两个表有关联关系时,拿一去查多, 或拿多去查一
BookInfo  (一)
HeroInfo  (多)
"""

# 一查多: 通过book要查到英雄
# 一模型对象.多方模型名小写_set.all()/filter()

# book = BookInfo.objects.get(id=1)
# hero = book.heroinfo_set
# hero = book.heroinfo_set.all()
# hero = book.heroinfo_set.filter(hgender=0)


# 多查一: 通过hero查询book
# 多.外键
# hero = HeroInfo.objects.get(hname='郭靖')
# book = hero.hbook  #  多.外键 就拿到了对应的一
# book = hero.hbook_id
# print(book)





"""
关联过滤查询:  拿别一方字段来作为过滤条件,默认不允许,如果非要这么写别一方字段,必须在前面指定此字段是谁
在过滤条件指定字段是谁的时候,如果字段是多的那方 就用多的这方模型名小写_字段名  
如果过滤条件字段是一的这方 外键字段名_字段名
一查多: HeroInfo.objects.filter(hbook__btitle='射雕英雄传')
多查一:  BookInfo.objects.filter(heroinfo__hname='郭靖')  
"""

# 一查多

# heros = HeroInfo.objects.filter(hbook__btitle='天龙八部')
# heros = HeroInfo.objects.filter(hbook__id=1)
# heros = HeroInfo.objects.filter(hbook_id=1)
# heros = HeroInfo.objects.filter(hbook__bread__gt=20)
# heros = HeroInfo.objects.filter(hbook__btitle__contains='龙', id=6)
# print(heros)


# 多查一
# book = BookInfo.objects.filter(heroinfo__hgender=1)
# book = BookInfo.objects.filter(heroinfo__id=1)
# print(book)

# 省: 一
# 市: 多
# citys = City.objects.filter(prov__pname='gd')
# class Province:
#     pname='xx'
#     # city_set
#     pass
#
# class City:
#     cname=''
#     prov = '指定所在的省'
#     pass
#
# sz = City.objects.get(id=1)
# gd = sz.prov_id

# gd.city_set.all()


# qs = BookInfo.objects.all()
# book = BookInfo.objects.get(id=1)
# print(qs)


# qs = BookInfo.objects.all()  # id =  1  bread = 12  # 只做转换SQL语句,没有执行查询

# qs  # id=1 bread=12 or 200
# [book.id for book in qs]
# [book.id for book in qs]


# print(id(qs[0]))
# print(qs[:])
# print(qs)
# print(qs[0])
# print(qs)

# book = BookInfo.objects.get(id=1)
# book.bread = 200
# book.save()
# print(id(book))
#
# print(qs[0].id , qs[0].bread)  # 1  200


# BookInfo.objects.filter(is_delete=False)
# BookInfo.objects.all()
# print(BookInfo.objects.abc())
# print(BookInfo.books.all())  # 如果当前模型自定义的过滤器 原有的objects属性就没有了
# 查询集缓存: 如果是在列表推导式中用查询集会有缓存  但print时每次都重新查询